package util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.commons.dbutils.DbUtils;
import org.apache.log4j.Logger;

public class DBUtils {
    
    private final static Logger LOG = Logger.getLogger(DBUtils.class);
    
    public final static String JDBC_DRIVER_ORACLE = "oracle.jdbc.driver.OracleDriver";
    public final static String JDBC_DRIVER_SQLSERVER = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
    public final static String JDBC_DRIVER_JTDS = "net.sourceforge.jtds.jdbc.Driver";

    public final static String URL_ORACLE = "jdbc:oracle:thin:@%1$s:%2$s:%3$s";
    public final static String URL_SQLSERVER = "jdbc:microsoft:sqlserver://%1$s:%2$s;DatabaseName=%3$s";
    public final static String URL_JTDS_SQL = "jdbc:jtds:sqlserver://%1$s:%2$s/%3$s:useUnicode=true:characterEncoding=GBK";
    
    public static String JNDI = "jdbc/default";
    
    public DBUtils() {
    }
    
    public DBUtils(String jndi) {
        JNDI = jndi;
    }
    
    public static void close(Connection... cs) {
        if (null != cs) {
            for (Connection c : cs) {
                DbUtils.closeQuietly(c);
            }
        }
    }
    
    public static void close(Statement... ss) {
        if (null != ss) {
            for (Statement s : ss) {
                DbUtils.closeQuietly(s);
            }
        }
    }
    
    public static void close(ResultSet... rs) {
        if (null != rs) {
            for (ResultSet r : rs) {
                DbUtils.closeQuietly(r);
            }
        }
    }

    public static void rollback(Connection... cs) {
        if (null != cs) {
            for (Connection c : cs) {
                try {
                    DbUtils.rollback(c);
                } catch (SQLException e) {
                    // quiet
                }
            }
        }
    }
    
    public static void commit(Connection... cs) throws SQLException {
        if (null != cs) {
            for (Connection c : cs) {
                c.commit();
            }
        }
    }
    
    public static DataSource getDataSource(String jndi) throws NamingException {
        DataSource ds = null;
        try {
            Context ctx = new InitialContext();
            
            // relative to standard JNDI root for J2EE app
            Context envCtx = (Context) ctx.lookup("java:comp/env");
            ds = (DataSource) envCtx.lookup(jndi);
        } catch (NamingException e) {
            throw e;
        }
        return ds;
    }
    
    /**
     * @param jndi
     * @return
     * @throws SQLException
     */
    public static Connection getConnction(String jndi) throws SQLException {
        Connection c = null;
        DataSource ds = null;
        try {
            try {
                ds = getDataSource(jndi);
            } catch (NamingException e) {
                e.printStackTrace();
            }
            if (null != ds) {
                c = ds.getConnection();
            }
        } catch (SQLException e) {
            throw e;
        }
        return c;
    }

    public static void setObject(PreparedStatement ps, Object[] values)
            throws SQLException {
        setObject(ps, values, null);
    }
    
    public static void setObject(PreparedStatement ps, Object[] values, int[] types)
            throws SQLException {
        if (null == types) {
            for (int i = 0; i < values.length; i++) {
                ps.setObject(i + 1, values[i]);
            }
        } else {
            for (int i = 0; i < values.length; i++) {
                ps.setObject(i + 1, values[i], types[i]);
            }
        }
        
    }
    
    public static List<Map<String, Object>> executeQuery(Connection c, String sql)
            throws SQLException {
        return executeQuery(c, sql, null, null);
    }
    
    public static List<Map<String, Object>> executeQuery(Connection c, String sql, Object[] values,
            int[] types) throws SQLException {
        List<Map<String, Object>> list = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = c.prepareStatement(sql);
            if (null != values && null != types) {
                setObject(ps, values, types);
            }
            rs = ps.executeQuery();
            ResultSetMetaData md = rs.getMetaData();
            int i = md.getColumnCount();
            while (rs.next()) {
                if (null == list) {
                    list = new ArrayList<Map<String, Object>>(0);
                }
                Map<String, Object> m = new HashMap<String, Object>(0);
                for (int j = 0; j < i; j++) {
                    m.put(md.getColumnLabel(j + 1), rs.getObject(j + 1));
                }
                list.add(m);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            close(rs);
            close(ps);
        }
        return list;
    }
    
    public static boolean execute(Connection c, String sql) throws SQLException {
        boolean b = false;
        Statement s = null;
        try {
            s = c.createStatement();
            b = s.execute(sql);
        } catch (SQLException e) {
            throw e;
        } finally {
            close(s);
        }
        return b;
    }
    
    public static int[] executeBatch(Connection c, String... sqls) throws SQLException {
        int[] ret = null;
        Statement s = null;
        try {
            s = c.createStatement();
            for (String sql : sqls) {
                s.addBatch(sql);
            }
            ret = s.executeBatch();
        } catch (SQLException e) {
            throw e;
        } finally {
            close(s);
        }
        return ret;
    }
    
    public static int executeUpdate(Connection c, String sql) throws SQLException {
        int ret = -1;
        int[] ints = executeBatch(c, sql);
        if (null != ints) {
            ret = ints[0];
        }
        return ret;
    }
    
    //============================================================================
    public final static int SQL_INSERT = 0;
    public final static int SQL_UPDATE = 1;
    public final static int SQL_DELETE = -1;

    private String table = "";
    private List columns = new ArrayList(0);
    private List types = new ArrayList(0);
    private List objects = new ArrayList(0);
    private List whereColumns = new ArrayList(0);
    private List whereTypes = new ArrayList(0);
    private List whereObjects = new ArrayList(0);
    
    
    public void setTable(String table) {
        this.table = table;
    }
    
    public void add(String column, Object object, int type) {
        this.columns.add(column);
        this.objects.add(object);
        this.types.add(new Integer(type));
    }
    
    public void addWhere(String column, Object object, int type) {
        this.whereColumns.add(column);
        this.whereObjects.add(object);
        this.whereTypes.add(new Integer(type));
    }
    
    public void clear() {
        this.table = "";
        this.columns.clear();
        this.objects.clear();
        this.types.clear();
        this.whereColumns.clear();
        this.whereObjects.clear();
        this.whereTypes.clear();
    }
    
    public int execute(int type) throws SQLException {
        int ret = -1;
        Connection c = null;
        try {
            c = getConnction(JNDI);
            if (null != c) {
                c.setAutoCommit(false);
                ret = this.execute(c, type);
                c.commit();
            }
        } catch (SQLException e) {
            rollback(c);
            throw e;
        } finally {
            close(c);
        }
        return ret;
    }
    
    public int execute(Connection c, int type) throws SQLException {
        int i = -1;
        switch (type) {
            case SQL_INSERT:
                i = this.executeInsert(c);
                break;
            case SQL_UPDATE:
                i = this.executeUpdate(c);
                break;
            case SQL_DELETE:
                i = this.executeDelete(c);
                break;
        }
        return i;
    }
    
    private int executeInsert(Connection c) throws SQLException {
        int ret = -1;
        String sql = this.getSql(SQL_INSERT);
        System.out.println(sql);
        if (null != c) {
            PreparedStatement ps = null;
            try {
                ps = c.prepareStatement(sql);
                for (int i = 0; i < this.columns.size(); i++) {
                    ps.setObject(i + 1, this.objects.get(i), ((Integer) this.types.get(i)).intValue());
                }
                ret = ps.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                throw e;
            } finally {
                close(ps);
            }
        }
        return ret;
    }
    
    private int executeUpdate(Connection c) throws SQLException {
        int ret = -1;
        String sql = this.getSql(SQL_UPDATE);
        System.out.println(sql);
        if (null != c) {
            PreparedStatement ps = null;
            try {
                ps = c.prepareStatement(sql);
                for (int i = 0; i < this.columns.size(); i++) {
                    ps.setObject(i + 1, this.objects.get(i), ((Integer) this.types.get(i)).intValue());
                }
                for (int i = 0; i < this.whereColumns.size(); i++) {
                    ps.setObject(i + this.columns.size() + 1, this.whereObjects.get(i),
                            ((Integer) this.whereTypes.get(i)).intValue());
                }
                ret = ps.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                throw e;
            } finally {
                close(ps);
            }
        }
        return ret;
    }
    
    private int executeDelete(Connection c) throws SQLException {
        int ret = -1;
        String sql = this.getSql(SQL_DELETE);
        System.out.println(sql);
        if (null != c) {
            PreparedStatement ps = null;
            try {
                ps = c.prepareStatement(sql);
                for (int i = 0; i < this.whereColumns.size(); i++) {
                    ps.setObject(i + 1, this.whereObjects.get(i),
                            ((Integer) this.whereTypes.get(i)).intValue());
                }
                ret = ps.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                throw e;
            } finally {
                close(ps);
            }
        }
        return ret;
    }
    
    public String getSql(int type) {
        String sql = null;
        switch (type) {
            case SQL_INSERT:
                sql = this.getSqlInsert();
                break;
            case SQL_UPDATE:
                sql = this.getSqlUpdate();
                break;
            case SQL_DELETE:
                sql = this.getSqlDelete();
                break;
        }
        return sql;
    }
    
    private String getSqlInsert() {
        String[] params = new String[this.columns.size()];
        Arrays.fill(params, "?");
        
        String sql = "INSERT INTO #TABLE# (#COLUMNS#) VALUES (#PARAMS#)";
        sql = sql.replaceFirst("#TABLE#", this.table);
        sql = sql.replaceFirst("#COLUMNS#", this.columns.toString().replaceAll("[\\[\\]]", ""));
        sql = sql.replaceFirst("#PARAMS#", Arrays.asList(params).toString().replaceAll("[\\[\\]]", ""));
        return sql;
    }
    
    private String getSqlUpdate() {
        List params = new ArrayList(this.columns.size());
        for (int i = 0; i < this.columns.size(); i++) {
            params.add(this.columns.get(i) + " = ?");
        }
        StringBuffer where = new StringBuffer();
        for (int i = 0; i < this.whereColumns.size(); i++) {
            where.append(" AND " + this.whereColumns.get(i) + " = ?");
        }
        
        String sql = "UPDATE #TABLE# SET #PARAMS# WHERE 1 = 1 #WHERE#";
        sql = sql.replaceAll("#TABLE#", this.table);
        sql = sql.replaceFirst("#PARAMS#", params.toString().replaceAll("[\\[\\]]", ""));
        sql = sql.replaceFirst("#WHERE#", where.toString());
        return sql;
    }
    
    private String getSqlDelete() {
        StringBuffer where = new StringBuffer();
        for (int i = 0; i < this.whereColumns.size(); i++) {
            where.append(" AND " + this.whereColumns.get(i) + " = ?");
        }
        
        String sql = "DELETE FROM #TABLE# WHERE 1 = 1 #WHERE#";
        sql = sql.replaceAll("#TABLE#", this.table);
        sql = sql.replaceFirst("#WHERE#", where.toString());
        return sql;
    }
    
}
